package com.situ.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.bean.StudentBean;

public class StuDao implements StuDaoible {

    @Override
    public List<StudentBean> getAllStu() throws SQLException {
        JDBCUtils.connDB("jdbc.properties");
        
        String sql = "select * from student";
        ResultSet rs = JDBCUtils.searchD(sql, null);
        List<StudentBean> stuList = new ArrayList<StudentBean>();
        while (rs.next()) {
            StudentBean st = new StudentBean();
            st.setStuId(rs.getInt(1));
            st.setStuName(rs.getString(2));
            st.setAge(rs.getInt(3));
            st.setSex(rs.getInt(4));
            st.setMajorId(rs.getInt(5));
            st.setMajorName(rs.getString(6));
            stuList.add(st);
        }
        
        JDBCUtils.disconnects();
        return stuList;
    }

    @Override
    public StudentBean getStuByName(String stuName) throws SQLException {
        JDBCUtils.connDB("jdbc.properties");

        String sql = "SELECT * FROM student where stu_name = ?;";
        Object[] arr = new Object[1];
        arr[0] = stuName;
        ResultSet rs = JDBCUtils.searchD(sql, arr);
        StudentBean st = new StudentBean();
        if (rs.next()) {
            st.setStuId(rs.getInt(1));
            st.setStuName(rs.getString(2));
            st.setAge(rs.getInt(3));
            st.setSex(rs.getInt(4));
            st.setMajorId(rs.getInt(5));
            st.setMajorName(rs.getString(6));
        }

        JDBCUtils.disconnects();
        return st;
    }

    @Override
    public int insertStu(StudentBean st) throws SQLException {
        JDBCUtils.connDB("jdbc.properties");

        String sql = "INSERT INTO student (stu_name,age,sex,major_id,major_name) VALUES(?, ?, ?, ?, ?);";
        // INSERT INTO student (stu_name, age, sex, major_id, major_name) VALUES('zz', 21, 1, 101, 'zzz');
        Object[] arr = new Object[5];
        if (st != null) {
            arr[0] = st.getStuName();
            arr[1] = st.getAge();
            arr[2] = st.getSex();
            arr[3] = st.getMajorId();
            arr[4] = st.getMajorName();
        }
        int n = JDBCUtils.updateU(sql, arr);

        JDBCUtils.disconnects();
        return n;
    }

    @Override
    public List<StudentBean> getStuByMajorId(Integer majorId) throws SQLException {
        JDBCUtils.connDB("jdbc.properties");

        String sql = "select * from student where major_id = ?";
        Object[] arr = new Object[1];
        arr[0] = majorId;
        ResultSet rs = JDBCUtils.searchD(sql, arr);
        List<StudentBean> stuList = new ArrayList<StudentBean>();
        while (rs.next()) {
            StudentBean st = new StudentBean();
            st.setStuId(rs.getInt(1));
            st.setStuName(rs.getString(2));
            st.setAge(rs.getInt(3));
            st.setSex(rs.getInt(4));
            st.setMajorId(rs.getInt(5));
            st.setMajorName(rs.getString(6));
            stuList.add(st);
        }

        JDBCUtils.disconnects();
        return stuList;
    }

}
